package july.android.cakes.service;

import java.io.UnsupportedEncodingException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional
public class SClass {

    @Autowired
    private JdbcTemplate jdbc;

    public String getcourseName(String id) {
        return this.jdbc.queryForMap("select nname from course where course.id=?", id).get("nname").toString();
    }

    //根据传入的学生学号获取其上的所有课程
    public List<Map<String, Object>> getcourse(String id) {
        return this.jdbc.queryForList("select teacher.icon,course.nname,course.id from teacher,course,cou2stu,cou2tea where course.id=cou2tea.cou_id and teacher.id=cou2tea.tea_id and course.id=cou2stu.cou_id and stu_id=?", id);
    }

    //根据传入的学生学号获取其上的所有课程和加入的讨论组
    public List<Map<String, Object>> getcd(String id) {
        List<Map<String, Object>> list = getcourse(id);
        List<Map<String, Object>> temp = getdiscussion(id);
        list.addAll(temp);
        return list;
    }

    //根据学生学号获取专业号
    public String getmajorid(String id) {
        return String.valueOf((this.jdbc.queryForMap("select ma_id from student where id=?", id)).get("ma_id"));
    }

    //根据学生学号获取年级
    public String getgrade(String id) {
        return String.valueOf((this.jdbc.queryForMap("select grade from student where id=?", id)).get("grade"));
    }

    //根据传入的学生学号获得其对应的课程的所有群组信息
    public List<Map<String, Object>> getgroupmessage(String id) {
        String grade = getgrade(id);
        String major_id = getmajorid(id);
        return this.jdbc.queryForList("select id,nname,chat,pull,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date2 from cou_chat,cou2stu,course where cou2stu.cou_id=cou_chat.cou_id and course.id=cou_chat.cou_id and stu_id=? and grade=? and major_id=?", id, grade, major_id);
    }

    //根据传入的“学生学号、课程号”获取该课程该同学应得的信息
    public List<Map<String, Object>> getSingroupmessage(String id, String stu_id) {
        List<Map<String, Object>> subList = getgroupmessage(stu_id);
        List<Map<String, Object>> te=getdiscussion(stu_id);
        subList.addAll(te);
        List<Map<String, Object>> temp = new LinkedList();
        for (Map<String, Object> item : subList) {
           // System.out.println(item);
            if (item.get("id").toString().equals(id)) {
                
                String pull = item.get("pull").toString();
                if (pull.length() == 13) {
                    item.putAll(jdbc.queryForMap("select nname as stu_name,icon from student where id=?", pull));
                } else {
                    item.putAll(jdbc.queryForMap("select nname as tea_name,icon from teacher where id=?", pull));
                }
                temp.add(item);
            }
        }
        return temp;
    }

    //根据传入的学生学号获得其对应的课程的群组消息的最新时间
    public List<Map<String, Object>> getdate(String id) {
        String grade = getgrade(id);
        String major_id = getmajorid(id);
        return this.jdbc.queryForList("select nname,DATE_FORMAT(max(ttime),'%y-%m-%d %H:%i:%s')as date4 from cou_chat,cou2stu,course where cou2stu.cou_id=cou_chat.cou_id and course.id=cou_chat.cou_id and stu_id=? and grade=? and major_id=? group by nname", id, grade, major_id);
    }

    //根据传入的学生学号判断是什么专业
    public String get_major(String id) {
        return (String) (this.jdbc.queryForMap("select major.nname from student,major where student.ma_id=major.id and student.id=?", id)).get("nname");
    }

    /*//根据传入的学生学号判断其是否加入讨论组，若加入则显示讨论组名字及讨论组消息
    public List<Map<String, Object>> getdiscussion(String id) {
        return this.jdbc.queryForList("select discussion_group.id,nname,pull,chat,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date3 from dis_chat,dis2stu,discussion_group where dis_chat.g_id=discussion_group.id and dis2stu.g_id=discussion_group.id and stu_id=?", id);
    }*/
    //根据传入的学生学号判断其是否加入讨论组，若加入则显示讨论组名字及讨论组消息
    public List<Map<String, Object>> getdiscussion(String id) {
        return this.jdbc.queryForList("select discussion_group.id,nname,discussion_group.icon,dis_chat.chat,dis_chat.pull,DATE_FORMAT(dis_chat.ttime,'%y-%m-%d %H:%i:%s')as date2 from dis2stu,discussion_group,dis_chat where dis2stu.g_id=discussion_group.id and discussion_group.id=dis_chat.g_id and dis2stu.stu_id=?", id);
    }

    //根据传入的学生学号及其针对某门课程发布的课程消息内容插入数据库
    public void addcoursemessage(String id, String cou_name, String chat) {
        String cou_id = String.valueOf((this.jdbc.queryForMap("select id from course where nname=?", cou_name)).get("id"));
        String major_id = getmajorid(id);
        String grade = getgrade(id);
        this.jdbc.update("insert into cou_chat(cou_id,pull,chat,major_id,grade) values(?,?,?,?,?)", Integer.parseInt(cou_id), id, chat, major_id, grade);
    }

    //根据传入的学生学号，讨论组号及其讨论组发布的消息内容插入数据库，前台应保存讨论组号
    public void addgroupmessage(String stu_id, String dis_id, String chat) {
        this.jdbc.update("insert into dis_chat values(?,?,?)", Integer.parseInt(dis_id), stu_id, chat);
    }

    //获取某个学生的所有老师资料
    public List<Map<String, Object>> getteacher(String id) {
        String stu_major = get_major(id);
        String stu_grade = getgrade(id);
        return this.jdbc.queryForList("select teacher.id,teacher.nname,icon,tel,email,address from teacher,tea2ma,major,tea_contacts where teacher.id=tea_contacts.tea_id and tea2ma.ma_id=major.id and tea_contacts.tea_id=tea2ma.tea_id and major.nname=? and grade=?", stu_major, stu_grade);
    }

    //根据学生学号获取其上的所有课程的名字和老师之间的对应关系
    public List<Map<String, Object>> gettea_cou(String id) {
        String major_id = getmajorid(id);
        String grade = getgrade(id);
        return this.jdbc.queryForList("select distinct course.nname,teacher.id,teacher.icon,teacher.nname from teacher,course,cou2stu,cou2tea,course_time where course.id=cou2tea.cou_id and teacher.id=cou2tea.tea_id and course_time.cou_id=course.id and ma_id=? and grade=? and course.id=cou2stu.cou_id and stu_id=?", major_id, grade, id);
    }

    //根据传入的教师工号及当前登录的学生学号获取教师上传的所有课件的详细信息
    public List<Map<String, Object>> getfiles(String tea_id, String stu_id) {
        String major = get_major(stu_id);
        String major_id = (this.jdbc.queryForMap("select id from major where nname=?", major)).get("id").toString();
        System.out.println(major_id);
        String stu_grade = getgrade(stu_id);
        return this.jdbc.queryForList("select files.id,files.nname,files.url,files.ttime,files.remark from files,tea2files,major2files where files.id=tea2files.f_id and files.id=major2files.f_id and tea2files.tea_id=? and major2files.ma_id=? and major2files.stu_grade=?", tea_id, Integer.parseInt(major_id), stu_grade);
    }

}
